-- **************************************************************************
--    Project Name:   干线车辆装载率
--    Job Name:       jms_dm__dm_tms_trunk_load_factor_basic_dt
--    Description :   计算干线线路段、线路的装载体积率、装载重率、装载票率
--    Author :        禹铄
--    date：          2022/02/15
-- **************************************************************************
--    Relation ：
--    jms_dm.dm_tms_trunk_load_factor_basic_dt << [
--          jms_dwd.dwd_wide_tms_trunk_shipno_road_waybill_dt,
--          jms_dim.dim_lmdm_sys_network,
--          jms_dim.dim_sys_manage_region,
--          jms_dim.dim_tms_mainline_vehicleloading,
--          jms_dm.dm_tms_shipment_noweight_detail_dt,
--          jms_dm.dm_trunk_error_scan_detail,
--          jms_dwd.dwd_tab_barscan_cycle_transfer_record_base_dt,
--      ]
-- **************************************************************************
--    modify by 禹铄 2022/3/14 对核载体积计算重新取值计算
--    modify by 禹铄 2022/3/15 使用事实表取出所有中心+车型
--    modify by 禹铄 2022/04/11 修改核载取数逻辑
--    modify by 禹铄 2022/05/05  新增错扫明细汇总数据
--    modify by 禹铄 2022/08/05 线路汇总线路率值计算新增策略
--    modify by 禹铄 2022/10/26 新增应装循环袋、原循环袋去重
-- **************************************************************************

create temporary function array_position as 'com.github.aaronshan.functions.array.UDFArrayPosition' using jar 'hdfs:///hive_udf/hive-third-functions-2.2.0-shaded.jar';

with tmp_network as (
    select a.code,                 -- 网点code
           c.name as manage_name,  -- 管理大区名字
           c.code as manage_code,  -- 管理大区code
           a.regional_desc,        -- 大区名字
           a.regional_id,          -- 大区id
           a.provider_desc,        -- 省份
           a.provider_id,          --省份id
           a.financial_center_desc,-- 财务大区名字
           a.financial_center_code -- 财务大区code
    from jms_dim.dim_lmdm_sys_network a
             left join jms_dim.dim_lmdm_sys_network b
                       on a.financial_center_code = b.code
             left join jms_dim.dim_sys_manage_region c
                       on b.id = c.proxy_network_id),

     tmp_wide as (
         select a.shipment_id,
                a.shipment_no,
                a.shipment_name,
                a.billcode,
                a.packagecode,
                a.package_inside_charge_weight,
                a.package_total_volume,
                a.package_total_weight,
                a.terminal_dispatch_code,
                a.start_site_code,
                a.start_site_name,
                a.end_site_code,
                a.end_site_name,
                a.shipment_site_cnt,
                a.vehicleline_id,
                a.vehicleline_code,
                a.vehicleline_name,
                a.shifts,
                a.business_attribute,
                a.operation_model,
                a.shipment_type,
                a.planned_departure_time,
                a.planned_arrival_time,
                a.actual_departure_time,
                a.actual_arrival_time,
                a.total_runtime,
                a.total_intransittime,
                a.span_days,
                a.standard_speed,
                a.mileage,
                a.load_site_sort_num,
                a.load_site_scantime,
                a.load_site_code,
                a.load_site_name,
                a.load_site_planned_departure_time,
                a.load_site_app_departure_time,
                a.load_simple_name,
                a.unload_site_sort_num,
                a.unload_site_scantime,
                a.unload_site_code,
                a.unload_site_name,
                a.unload_site_planned_arrival_time,
                a.unload_site_app_arrival_time,
                a.unload_simple_name,
                a.next_site_sort_num,
                a.next_station_code,
                a.next_station_name,
                a.next_site_planned_arrival_time,
                a.next_site_app_arrival_time,
                a.next_simple_name,
                a.shipstop_roadsection_id,
                a.shipstop_mileage,
                a.first_center_network_code,
                a.first_center_vehicletype_name,
                nvl(c.averagevolume,0.0082) as first_center_averagevolume, -- 所有单对应的原始中心的核载体、重、票、平均体积  默认值0.0082
                a.plate_number,
                a.vehicletype_name,
                a.trailer_number,
                a.carrier_id,
                a.carrier_type,
                a.carrier_name,
                a.driver_name,
                a.driver_contact,
                b.vehicleweight    vehicleweight,              -- 本任务的第一中心的核载重量
                b.vehiclevolume    vehicle_volume,             -- 本任务的第一中心的核载体积
                a.quotation_model,
                a.billing_way,
                a.settlement_vehicle_type_id,
                a.settlement_vehicle_type_name,
                a.freight_generate_status,
                a.shipment_state,
                a.is_ballast,
                a.is_stop,
                a.is_assist_line,
                a.vehicleline_type,
                a.has_out,
                a.strategy_line,
                a.grade
         from jms_dwd.dwd_wide_tms_trunk_shipno_road_waybill_dt a
                  left join jms_dim.dim_tms_mainline_vehicleloading b
                            on b.distributecode is null -- 先用本任务的车型取 （再用本任务中心code+车型关联 取核载表的值，如果取出则使用该值，如果取不出则就取该处关联出来的值）
                                and a.vehicletype_name = b.vehicletype
                  left join jms_dim.dim_tms_mainline_vehicleloading c -- 先用本任务的车型取 （再用原始中心code+车型关联 取核载表的值，如果取出则使用该值，如果取不出则就取该处关联出来的值）
                            on c.distributecode is null
                                and a.vehicletype_name = c.vehicletype
         where a.dt >= date_add(date_add('{{ execution_date | cst_ds }}',1), -3)
           and a.dt < date_add('{{ execution_date | cst_ds }}',1)
           and a.road_id = 1
           and a.billcode is not null          -- 单号为null去除
           and substr(a.billcode, 1, 2) = 'JT' -- 只要JT单号
           and a.shipment_state <> 5 -- 剔除作废任务号
     ),
     tmp_detail as (select e.billcode
                            ,                                                                                         -- 运单号
                           e.packagecode
                            ,                                                                                         -- 包号
                           e.planned_departure_time                                            date_time
                            ,                                                                                         -- 计划发车时间
                           case when e.has_out = 1 then '是' when e.has_out = 2 then '否' end as has_out
                            ,                                                                                         -- 是否兜底
                           e.shipment_no
                            ,                                                                                         -- 运单第一中心对应的车型
                           nvl(l.vehiclevolume, e.vehicle_volume)                              vehiclevolume
                            ,                                                                                         -- 运单 在本任务中  对应车型 对应的核载体积
                           nvl(l.vehicleweight, e.vehicleweight)                               vehicleweight
                            ,                                                                                         -- 运单 在本任务中  对应车型 对应的核载重量
--                            e.first_center_vehiclecount                                         vehiclecount
--                             ,                                                                                         -- 运单 原始第一中心对应车型 对应的核载票数
                           nvl(k.averagevolume, e.first_center_averagevolume)                  averagevolume
                            ,                                                                                         -- 运单 在原始任务中 第一中心 对应车型 对应的核载 平均体积 *(如果原始中心+原始中心车型能关联上则取，关联不上就取使用本任务车型取的值)
                           e.shipment_site_cnt
                            ,                                                                                         -- 途径站点数
                           e.vehicleline_name
                            ,                                                                                         -- 参考车线名称
                           e.load_site_code                                                    startcode
                            ,                                                                                         -- 发出站点代码
                           concat(e.load_site_name, '-', e.load_site_code)                     startdistribute
                            ,                                                                                         -- 发车站点名字 + 发车站点代码
                           e.load_site_sort_num                                                start_sort_num
                            ,                                                                                         -- 发出站点的 序号
                           e.next_station_code                                                 endcode
                            ,                                                                                         -- 到达站点代码
                           concat(e.next_station_name, '-', e.next_station_code)               enddistribute
                            ,                                                                                         -- 到达站点名字 + 到达站点代码
                           e.next_site_sort_num                                                end_sort_num
                            ,                                                                                         -- 到达站点序号
                           concat(e.load_simple_name, '-', e.next_simple_name)                 linepartname
                            ,                                                                                         -- 发出站点-到达站点
                           e.shipstop_mileage                                                  mileage
                            ,                                                                                         -- 里程数

                           case
                               when e.business_attribute = 1
                                   then '正班'
                               when e.business_attribute = 2
                                   then '加班'
                               end                                                          as business_attribute
                            ,                                                                                         -- 任务类型
                           case
                               when e.operation_model = 1
                                   then '单边'
                               when e.operation_model = 2
                                   then '对开'
                               when e.operation_model = 3
                                   then '往返'
                               end                                                          as operation_model
                            ,                                                                                         --运行模式

                           e.plate_number
                            ,                                                                                         -- 车牌号
                           e.vehicletype_name
                            ,                                                                                         -- 车型
                           e.load_site_planned_departure_time                                  planned_departure_time -- 规划发车时间
                            ,
                           e.load_site_app_departure_time                                      app_departure_time     --实际发车时间（此处逻辑使用app离开时间）
                            ,
                           case
                               when e.load_site_planned_departure_time >= e.load_site_app_departure_time
                                   then '是'
                               when e.load_site_planned_departure_time < e.load_site_app_departure_time
                                   then '否'
                               end                                                          as if_ontime
                            ,                                                                                         -- 是否准点,
                           e.unload_site_planned_arrival_time                                  planned_arrival_time,  --计划到达时间
                           e.unload_site_app_arrival_time                                      app_arrival_time,      --app到达时间
                           case
                               when e.unload_site_app_arrival_time <= e.unload_site_planned_arrival_time
                                   then '否'
                               when e.unload_site_app_arrival_time > e.unload_site_planned_arrival_time
                                   then '是'
                               end                                                          as if_late
                            ,                                                                                         --是否晚点
                           e.shipment_name                                                     routes_name
                            ,                                                                                         -- 线路名称,
                           e.vehicleline_code
                            ,                                                                                         -- 线路编码,
                           e.shipstop_roadsection_id
                            ,                                                                                         -- 路段id
                           h.regional_desc                                                     regional_desc_s
                            ,                                                                                         -- 发出站点-大区名称
                           j.regional_desc                                                     regional_desc_r
                            ,                                                                                         -- 到达站点-大区名称
                           h.manage_name                                                       manage_regional_desc_s
                            ,                                                                                         -- 发出站点-管理大区名称
                           j.manage_name                                                       manage_regional_desc_r
                            ,                                                                                         -- 到达站点-管理大区名称
                           e.package_inside_charge_weight                                      package_inside_charge_weight
                            ,                                                                                         -- 包裹计费重量,单位千克
                           e.package_total_volume                                              package_total_volume
                            ,                                                                                         -- 包裹总体积,单位立方厘米
                           e.package_total_weight                                              package_total_weight
                            ,                                                                                         -- 包裹总重量,单位千克
                           h.financial_center_desc                                             financial_center_desc_s
                            ,                                                                                         -- 发出站点-财务中心名称
                           h.provider_desc                                                     provider_desc_s
                            ,                                                                                         -- 发出站点-省份
                           j.financial_center_desc                                             financial_center_desc_r
                            ,                                                                                         -- 到达站点-财务中心名称
                           j.provider_desc                                                     provider_desc_r
                            ,                                                                                         -- 到达站点-省份
                           h.financial_center_code                                             financial_center_code_s
                            ,                                                                                         -- 发出站点-财务中心code
                           h.regional_id                                                       regional_id_s
                            ,                                                                                         -- 发出站点-大区id
                           h.manage_code                                                       manage_regional_code_s
                            ,                                                                                         -- 发出站点-管理大区code
                           h.provider_id                                                       provider_id_s
                            ,                                                                                         -- 发出站点-省份id
                           j.financial_center_code                                             financial_center_code_r
                            ,                                                                                         -- 到达站点-财务中心code
                           j.regional_id                                                       regional_id_r
                            ,                                                                                         -- 到达站点-大区id
                           j.manage_code                                                       manage_regional_code_r
                            ,                                                                                         -- 到达站点-管理大区code
                           j.provider_id                                                       provider_id_r
                           -- 到达站点-省份id
                            ,
                concat(e.start_site_code,'-',e.end_site_code) s_e_code                                --
                           ,e.strategy_line    -- 是否战略
                           ,e.grade
                           ,cast(e.planned_departure_time as date)                              dt
                    from tmp_wide e
                             left join
                         tmp_network h -- 网点资料
                         on
                             h.code = e.load_site_code
                             left join
                         tmp_network j
                         on
                             j.code = e.next_station_code
                             left join jms_dim.dim_tms_mainline_vehicleloading k
                                       on e.first_center_network_code = k.distributecode
                                           and e.first_center_vehicletype_name = k.vehicletype
                             left join jms_dim.dim_tms_mainline_vehicleloading l
                                       on e.start_site_code = l.distributecode
                                           and e.vehicletype_name = l.vehicletype
     ),
--  汇总 =
     tmp_linepart as (
         select mm.startcode                    as senderdistributecode
              , mm.endcode                      as receiverdistributecode
              , mm.has_out                      as hasout
              , max(mm.shipment_site_cnt)       as shipment_site_cnt
              , max(mm.regional_desc_s)         as senderregionalname
              , max(mm.manage_regional_desc_s)  as sender_manage_regionalname   --
              , max(mm.start_sort_num)          as start_sort_num
              , max(mm.regional_desc_r)         as receiverregionalname
              , max(mm.manage_regional_desc_r)  as receiver_manage_regionalname --
              , max(mm.end_sort_num)            as end_sort_num
              , max(mm.date_time)               as datetime
              , mm.shipment_no                  as shipmentno
              , max(mm.routes_name)             as shipmentname
              , max(mm.vehicleline_name)        as vehiclelinename
              , max(mm.vehicleline_code)        as vehiclelinecode
              , max(mm.linepartname)            as linename
              , max(mm.mileage)                 as totalmileage
              , max(mm.business_attribute)      as tasktype
              , max(mm.vehiclevolume)           as vehiclevolume
              ,                                                                 -- 运单 本任务的第一中心  对应的核载体积
             max(mm.vehicleweight * 1000)       as vehicleweight
              ,                                                                 -- 运单第一中心 本任务的第一中心  对应的核载重量 转换成千克
             sum(mm.averagevolume)              as averagevolume
              -- 运单第一中心 对应车型 对应的核载 平均体积 之和
              , round(sum
                          (
                              case
                                  when nvl(mm.package_inside_charge_weight, 0) < 1000
                                      and mm.billcode like 'JT%'
                                      then nvl(mm.package_inside_charge_weight, 0)
                                  else 0
                                  end
                          )
             , 4)                               as linepartweight
              ,                                                                 --线路段重量(单位为kg)
             round(sum
                       (
                               case
                                   when mm.billcode like 'JT%'
                                       then nvl(mm.package_total_volume, 0)
                                   else 0
                                   end / 1000000
                       )
                 , 4)                           as linepartvolume
              ,                                                                 -- 线路段体积(转换立方米)
             round(sum
                       (
                           case
                               when mm.billcode like 'JT%'
                                   then 1
                               else 0
                               end
                       )
                 , 4)                           as linepartcount
              -- 线路段票数
              , sum
             (
                 case
                     when mm.billcode like 'JT%'
                         and mm.packagecode is null
                         then 1
                     else 0
                     end
             )
                                                as signalcount
              , max(mm.financial_center_desc_s) as startparentnetworkname
              , max(mm.provider_desc_s)         as startproviderdesc
              , max(mm.startdistribute)         as startcentername
              , max(mm.financial_center_desc_r) as endparentnetworkname
              , max(mm.provider_desc_r)         as endproviderdesc
              , max(mm.enddistribute)           as endcentername
              , max(mm.operation_model)         as operationmodel
              , max(mm.plate_number)            as platenumber
              , max(mm.vehicletype_name)        as vehicletypename
              , max(mm.planned_departure_time)  as planneddeparturetime
              , max(mm.app_departure_time)      as actualdeparturetime
              , max(mm.if_ontime)               as ispunctuality
              , max(mm.planned_arrival_time)    as plannedarrivaltime
              , max(mm.app_arrival_time)        as actualarrivaltime
              , max(mm.if_late)                 as islate
              , max(mm.financial_center_code_s) as startparentnetworkcode
              , max(mm.regional_id_s)           as senderregionalid
              , max(mm.manage_regional_code_s)  as sender_manage_regionalcode
              , max(mm.provider_id_s)           as startproviderid
              , max(mm.financial_center_code_r) as endparentnetworkcode
              , max(mm.regional_id_r)           as receiverregionaid
              , max(mm.manage_regional_code_r)  as receiver_manage_regionacode
              , max(mm.provider_id_r)           as endproviderid
              ,max(s_e_code)  s_e_code
              ,max(strategy_line) strategy_line
              ,max(grade) grade
         from tmp_detail mm
         where mm.dt >= date_add(date_add('{{ execution_date | cst_ds }}',1), -3)
           and mm.dt < date_add('{{ execution_date | cst_ds }}',1)
         group by mm.shipment_no
                , mm.endcode
                , mm.has_out
                , mm.startcode
     ),
     -- 20221018 循环袋原逻辑
     tmp_record as (
         select shipment_no, recycle_bags_number, recycle_bags_weight
         from (select a.shipment_no,
                      a.recycle_bags_number,
                      a.recycle_bags_weight,
                      row_number() over (partition by shipment_no order by scan_time desc ) rk
               from jms_dwd.dwd_tab_barscan_cycle_transfer_record_base_dt a) x
         where x.rk = 1
     ),
     -- 异常重量数据
     tmp_problem as (
         select a.shipment_no,
                a.sender_distribute_code,
                a.receiver_distribute_code,
                sum(if(a.weight_exception_type = 'no_weight', 1, 0))              no_weight_cnt,
                sum(if(a.weight_exception_type = 'more1000_weight', 1, 0))        more1000_weight_cnt,
                sum(if(a.weight_exception_type = 'less1000_more30_weight', 1, 0)) not_nomarl_cnt
         from jms_dm.dm_tms_shipment_noweight_detail_dt a
         where a.dt >= date_add(date_add('{{ execution_date | cst_ds }}',1), -7)
           and a.dt <= date_add('{{ execution_date | cst_ds }}',1)
         group by a.shipment_no,
                  a.sender_distribute_code,
                  a.receiver_distribute_code
     ),
     -- 错扫数据
     tmp_error as (
         select shipment_no,
                scan_site_code,
                count(*) error_num
         from jms_dm.dm_trunk_error_scan_detail
         where dt >= date_add(date_add('{{ execution_date | cst_ds }}',1), -3)
           and dt < date_add('{{ execution_date | cst_ds }}',1)
         group by shipment_no, scan_site_code
     ),
     -- 20220922新逻辑
     tmp_ship_handling as (
         select shipment_no,
                `if`(avg(cnt) > 1, 1, 2) ship_handling_type -- 是否存在经停 的标志  如果均值大于1 则任务存在该干线任务中存在有某个站点 >2 ,则说明在某中心有装卸，则走策略1
         from (
                  select shipment_no,
                         site_code,
                         count(1) cnt
                  from (
                           select shipment_no    shipment_no,
                                  load_site_code site_code
                           from jms_dwd.dwd_wide_tms_trunk_shipno_road_waybill_dt
                           where dt >= date_add(date_add('{{ execution_date | cst_ds }}',1), -3)
                             and dt < date_add('{{ execution_date | cst_ds }}',1)
                             and billcode like 'JT%'
                           group by shipment_no, load_site_code
                           union all
                           select shipment_no      shipment_no,
                                  unload_site_code site_code
                           from jms_dwd.dwd_wide_tms_trunk_shipno_road_waybill_dt
                           where dt >= date_add(date_add('{{ execution_date | cst_ds }}',1), -3)
                             and dt < date_add('{{ execution_date | cst_ds }}',1)
                             and billcode like 'JT%'
                           group by shipment_no, unload_site_code
                       ) x
                  group by shipment_no, site_code
              ) y
         where y.site_code is not null
         group by shipment_no
     ),
    -- 20230403 --           计算路由中该任务号的始发配载转运中心、目的配载转运中心
    tmp_se as (select z.s_e_code,
                      concat_ws('/', collect_set(c.simple_name)) as c_start_n,
                      concat_ws('/', collect_set(d.simple_name)) as c_end_n
               from (select route_code_list,
                            concat(lst, '-', lst_lead)                                     s_e_code,
                            route_code_list[if(array_position(route_code_list, lst) = 1, 1,
                                               array_position(route_code_list, lst) - 1) -
                                            1]                                             head_code,
                            route_code_list[
                                    if(array_position(route_code_list, lst_lead) = route_code_list_lenth,
                                       route_code_list_lenth,
                                       array_position(route_code_list, lst_lead) + 1) - 1] tail_code
                     from (select id,
                                  route_code_list,
                                  route_code_list_lenth,
                                  lst,
                                  lead(lst, 1) over (partition by id order by idx) lst_lead
                           from (select a.id,
                                        split(route_code, '-')       as route_code_list,
                                        size(split(route_code, '-')) as route_code_list_lenth,
                                        tp.list                         lst,
                                        tp.idx
                                 from (select *
                                       from jms_dim.dim_yl_tms_route_base_dt a
                                       where dt = '{{ execution_date | cst_ds }}'
                                         and status = 1
                                         and route_type = 1) a
                                          lateral view posexplode(split(route_code, '-')) tp as idx, list) x) y
                     where lst_lead is not null) z
                        left join jms_dim.dim_network_whole_massage c
                                  on z.head_code = c.code
                        left join jms_dim.dim_network_whole_massage d
                                  on z.tail_code = d.code
                    group by z.s_e_code
        )
insert overwrite table jms_dm.dm_tms_trunk_load_factor_basic_dt partition(dt)
select x.senderdtributecode                                                                                 -- 发送网点代码
     , x.receiverdtributecode                                                                               -- 接收网点代码
     , x.senderregionalname                                                                                 -- 始发大区
     , x.receiverregionalname                                                                               -- 目的大区
     , x.sender_manage_regionalname                                                                         -- 始发管理大区
     , x.receiver_manage_regionalname                                                                       -- 目的管理大区
     , x.datetime                                                                                           -- 运行日期 （计划发车时间）
     , x.shipmentno                                                                                         --任务单号
     , x.vehiclelinename                                                                                    -- 线路名称
     , x.vehiclelinecode                                                                                    --线路编码
     , x.linename                                                                                           --线路段
     , x.totalmileage                                                                                       --线路段公里数
     , x.tasktype                                                                                           -- 任务类型
     , x.linepartweight                                                                                     -- 线路段 重量
     , x.linepartvolume                                                                                     -- 线路段 体积
     , x.linepartcount                                                                                      --线路段 票数
     , round(x.linepartweight_fz / x.vehicleweight, 4)                               as linepartweight_rate --线路段 重量 装载率
     , round(x.linepartvolume_fz / x.vehiclevolume, 4)                               as linepartvolume_rate --线路段 体积 装载率
     , round(x.linepartcount_fz / (x.vehiclevolume / (x.volume_sm / x.count_sm)), 4) as linepartcount_rate  --线路段 票数 装载率
     , x.startparentnetworkname                                                                             --出发代理区名称
     , x.startproviderdesc                                                                                  --出发省
     , x.startcentername                                                                                    -- 出发转运中心名称
     , x.endparentnetworkname                                                                               -- 目的代理区名称
     , x.endproviderdesc                                                                                    -- 目的省
     , x.endcentername                                                                                      -- 目的转运中心名称
     , x.operationmodel                                                                                     -- 运行模式
     , x.platenumber                                                                                        -- 车牌号
     , x.vehicletypename                                                                                    -- 车型
     , x.planneddeparturetime                                                                               -- 规划发车时间
     , x.actualdeparturetime                                                                                -- 实际发车时间
     , x.punctuality                                                                                        -- 是否准点
     , x.plannedarrivaltime                                                                                 -- 规划到车时间
     , x.actualarrivaltime                                                                                  -- 实际到车时间
     , x.late                                                                                               -- 是否晚点
     , x.vehicleweight                                                                                      -- 核载重量
     , x.vehiclevolume                                                                                      -- 核载体积
     , round(x.vehiclevolume / (x.volume_sm / x.count_sm))                                                  -- 核载票数 （四舍五入）
     , x.lineweight                                                                                         -- 线路重量
     , x.linevolume                                                                                         -- 线路体积
     , x.linecount                                                                                          -- 线路票数
     -- 因为所有线路段的线路率值都一样，所以根据任务号开窗 并且取A为始发的 核载的体、重、票 所以按照任务号分区，按照start_sort_num从小到大，取出的即为A站的核载体重票）
     , round(x.lineweight / first_value(x.vehicleweight) over (partition by x.shipmentno order by x.start_sort_num ),
             4)                                                                                             -- 线路 重量 装载率  因为所有线路段的线路率值都一样，所以根据任务号开窗 取A为始发的 核载的体、重、票
     , round(x.linevolume / first_value(x.vehiclevolume) over (partition by x.shipmentno order by x.start_sort_num),
             4)                                                                      as linevolume_rate     -- 线路 体积 装载率
     -- 此处逻辑较为复杂 因为核载的票数（分母） 是由其对应核载的体积 /（各票原始中心单票的体积之和/票数）算出来的，所以vehiclevolume、volume_sm、count_sm 三个值都要取到为A的，再用线路票数和核载票数做率值对比。
     , round(x.linecount /
             (first_value(vehiclevolume) over (partition by x.shipmentno order by x.start_sort_num)
                 /
              (first_value(volume_sm) over (partition by x.shipmentno order by x.start_sort_num)
                  /
               first_value(count_sm) over (partition by x.shipmentno order by x.start_sort_num)))
    , 4)                                                                             as linecount_rate      -- 线路 票数 装载率
     , x.startparentnetworkcode                                                                             -- 出发地代理商编码
     , x.senderregionalid                                                                                   -- 出发大区id
     , x.sender_manage_regionalcode                                                                         -- 出发管理大区code
     , x.startproviderid                                                                                    -- 出发省id
     , x.receiverregionaid                                                                                  -- 目的大区id
     , x.receiver_manage_regionacode                                                                        -- 目的管理大区code
     , x.endproviderid                                                                                      -- 目的省id
     , x.endparentnetworkcode                                                                               -- 目的地代理商编码
     , nvl(z.no_weight_cnt, 0)                                                                              -- 无重量票数
     , nvl(z.more1000_weight_cnt, 0)                                                                        -- 超大重量票
     , nvl(z.not_nomarl_cnt, 0)                                                                             -- 超标准重量票
     , nvl(y.recycle_bags_number, 0)                                                 as cirbag              -- 循环袋数
     , nvl(y.recycle_bags_weight, 0)                                                 as cirbagweight        -- 循环袋重量
     , x.shipmentname                                                                                       -- 任务名字
     , x.hasout                                                                                             -- 是否兜底
     , case
           when nvl(x.linepartvolume, 0) = 0
               and nvl(x.linepartcount, 0) = 0
               and nvl(y.recycle_bags_number, 0) >= 0
               and nvl(y.recycle_bags_weight, 0) >= 0
               then '是'
           else '否' end                                                              as issupplies          -- 是否物资线
     , date_add('{{ execution_date | cst_ds }}',1)                                                              as createdate          -- 创建时间
     , k.axle_number                                                                                        -- 车辆轴数
     , if(l.error_num > 0, '是', '否')                                                                        -- 是否有错扫
     , l.error_num                                                                                          -- 错扫票数
     -- 应装循环袋数量=（线路首发中心对应的核载重量-线路实际装载重量）/0.5kg 。   （商取整数，比如0.2，那就计为1票。14.5就计为15票）
          , if(first_value(x.vehicleweight) over (partition by x.shipmentno order by x.start_sort_num ) > x.lineweight,
               ceiling((first_value(x.vehicleweight) over (partition by x.shipmentno order by x.start_sort_num ) -
                        x.lineweight) / 0.5)
         , 0)                                                                             as should_cirbag       -- 应装循环袋数
     , tmp_se.c_start_n as c_start_n                                                                    --
     ,tmp_se.c_end_n as c_end_n                                                                       --
     ,x.strategy_line
     ,x.grade
     , cast(x.datetime as date)                                                      as dt                  -- 计划发车时间作为分区时间
from (
         select y.senderdistributecode                                         as               senderdtributecode           -- 发送网点代码
              , y.receiverdistributecode                                       as               receiverdtributecode         -- 接收网点代码
              , y.senderregionalname                                           as               senderregionalname           -- 始发大区
              , y.receiverregionalname                                         as               receiverregionalname         -- 目的大区
              , y.sender_manage_regionalname                                   as               sender_manage_regionalname   -- 始发管理大区
              , y.receiver_manage_regionalname                                 as               receiver_manage_regionalname -- 目的管理大区
              , y.datetime                                                     as               datetime                     -- 运行日期 （计划发车时间）
              , y.shipmentno                                                   as               shipmentno                   --任务单号
              , y.vehiclelinename                                              as               vehiclelinename              -- 线路名称
              , y.vehiclelinecode                                              as               vehiclelinecode              --线路编码
              , y.linename                                                     as               linename                     --线路段
              , y.totalmileage                                                 as               totalmileage                 --线路段公里数
              , y.tasktype                                                     as               tasktype                     -- 任务类型
              , y.linepartweight                                               as               linepartweight               -- 线路段装载率重量
              , y.linepartvolume                                               as               linepartvolume               -- 线路段装载率体积
              , y.linepartcount                                                as               linepartcount                --线路段装载率票数
              , sum(y.linepartweight)
                    over (partition by y.shipmentno,y.senderdistributecode)    as               linepartweight_fz            --线路段重量-汇总 ab+ac+ad /bc+bd/cd （该值为线路段装载率分子,分母为A/b/c的核载重量）
              , sum(y.linepartvolume)
                    over (partition by y.shipmentno,y.senderdistributecode)    as               linepartvolume_fz            -- 线路段体积-汇总 ab+ac+ad /bc+bd/cd （段装载率分子,分母为A/b/c的核载体积）
              , sum(y.linepartcount)
                    over (partition by y.shipmentno,y.senderdistributecode)    as               linepartcount_fz             -- 线路段载票量-汇总 ab+ac+ad /bc+bd/cd （段装载率分子,分母为A/b/c的核载票数）
              , y.startparentnetworkname                                       as               startparentnetworkname       --出发代理区名称
              , y.startproviderdesc                                            as               startproviderdesc            --出发省
              , y.startcentername                                              as               startcentername              -- 出发转运中心名称
              , y.endparentnetworkname                                         as               endparentnetworkname         -- 目的代理区名称
              , y.endproviderdesc                                              as               endproviderdesc              -- 目的省
              , y.endcentername                                                as               endcentername                -- 目的转运中心名称
              , y.operationmodel                                               as               operationmodel               -- 运行模式
              , y.platenumber                                                  as               platenumber                  -- 车牌号
              , y.vehicletypename                                              as               vehicletypename              -- 车型
              , y.start_sort_num
              , case
                    when y.start_sort_num = 1
                        then y.vehicleweight - (sum(if(y.start_sort_num < 1
                                                           and end_sort_num > 1, y.linepartweight, 0))
                                                    over (partition by shipmentno))
                    when y.start_sort_num = 2
                        then y.vehicleweight - (sum(if(y.start_sort_num < 2
                                                           and end_sort_num > 2, y.linepartweight, 0))
                                                    over (partition by shipmentno))
                    when y.start_sort_num = 3
                        then y.vehicleweight - (sum(if(y.start_sort_num < 3
                                                           and end_sort_num > 3, y.linepartweight, 0))
                                                    over (partition by shipmentno))
                    when y.start_sort_num = 4
                        then y.vehicleweight - (sum(if(y.start_sort_num < 4
                                                           and end_sort_num > 4, y.linepartweight, 0))
                                                    over (partition by shipmentno))
                    when y.start_sort_num = 5
                        then y.vehicleweight - (sum(if(y.start_sort_num < 5
                                                           and end_sort_num > 5, y.linepartweight, 0))
                                                    over (partition by shipmentno))
                    when y.start_sort_num = 6
                        then y.vehicleweight - (sum(if(y.start_sort_num < 6
                                                           and end_sort_num > 6, y.linepartweight, 0))
                                                    over (partition by shipmentno))
                    when y.start_sort_num = 7
                        then y.vehicleweight - (sum(if(y.start_sort_num < 7
                                                           and end_sort_num > 7, y.linepartweight, 0))
                                                    over (partition by shipmentno))
             end                                                                                vehicleweight                -- 核载 重量   a开头的线路段 ：直接取A的车核载  b开头的核载 = a的核载 - （ac+ad线路段的核载）  c开头的核载 = a的核载 - （ad+bd线路段的核载）
              , case
                    when y.start_sort_num = 1
                        then y.vehiclevolume - (sum(if(y.start_sort_num < 1
                                                           and end_sort_num > 1, y.linepartvolume, 0))
                                                    over (partition by shipmentno))
                    when y.start_sort_num = 2
                        then y.vehiclevolume - (sum(if(y.start_sort_num < 2
                                                           and end_sort_num > 2, y.linepartvolume, 0))
                                                    over (partition by shipmentno))
                    when y.start_sort_num = 3
                        then y.vehiclevolume - (sum(if(y.start_sort_num < 3
                                                           and end_sort_num > 3, y.linepartvolume, 0))
                                                    over (partition by shipmentno))
                    when y.start_sort_num = 4
                        then y.vehiclevolume - (sum(if(y.start_sort_num < 4
                                                           and end_sort_num > 4, y.linepartvolume, 0))
                                                    over (partition by shipmentno))
                    when y.start_sort_num = 5
                        then y.vehiclevolume - (sum(if(y.start_sort_num < 5
                                                           and end_sort_num > 5, y.linepartvolume, 0))
                                                    over (partition by shipmentno))
                    when y.start_sort_num = 6
                        then y.vehiclevolume - (sum(if(y.start_sort_num < 6
                                                           and end_sort_num > 6, y.linepartvolume, 0))
                                                    over (partition by shipmentno))
                    when y.start_sort_num = 7
                        then y.vehiclevolume - (sum(if(y.start_sort_num < 7
                                                           and end_sort_num > 7, y.linepartvolume, 0))
                                                    over (partition by shipmentno))
             end                                                                                vehiclevolume                -- '核载体积' a开头的线路段 ：直接取A的车核载  b开头的核载 = a的核载 - （ac+ad线路段的核载）  c开头的核载 = a的核载 - （ad+bd线路段的核载）
              -- '核载票量'   核载体积 / (volume_sm / count_sm)
              , sum(y.averagevolume)
                    over (partition by y.shipmentno , y.senderdistributecode)  as               volume_sm                    -- ab+ac+ad  or bc+bd or cd  体积和（ 用作求核载票量 每一票的原始中心的核载维护的平均体积 之和）
              , sum(y.linepartcount)
                    over (partition by y.shipmentno , y.senderdistributecode ) as               count_sm                     -- ab+ac+ad  or bc+bd or cd  票数之和（用作求核载票量 ）
              , case
                    when tmp_ship_handling.ship_handling_type = 1 then sum(
                            if(y.end_sort_num = y.shipment_site_cnt, y.linepartweight, 0))
                            over (partition by y.shipmentno)
                    when tmp_ship_handling.ship_handling_type = 2
                        then sum(nvl(y.linepartweight, 0)) over (partition by y.shipmentno) end lineweight                   -- 线路装载重量策略1 ae + be +ce + de（该值为线路装载率分子, 分母为A的核载重量）
              -- 线路装载重量策略2 AB+AC+AD+BC+BD+CD（该值为线路装载率分子, 分母为A的核载重量）
              , case
                    when tmp_ship_handling.ship_handling_type = 1 then sum(
                            if(y.end_sort_num = y.shipment_site_cnt, y.linepartvolume, 0))
                            over (partition by y.shipmentno)
                    when tmp_ship_handling.ship_handling_type = 2
                        then sum(nvl(y.linepartvolume, 0)) over (partition by y.shipmentno) end linevolume                   -- 线路装载体积策略1： ae + be +ce + de （该值为线路装载率分子,分母为A的核载体积）
              -- 线路装载体积策略2： AB+AC+AD+BC+BD+CD （该值为线路装载率分子,分母为A的核载体积）
              , case
                    when tmp_ship_handling.ship_handling_type = 1 then sum(
                            if(y.end_sort_num = y.shipment_site_cnt, y.linepartcount, 0))
                            over (partition by y.shipmentno)
                    when tmp_ship_handling.ship_handling_type = 2
                        then sum(nvl(y.linepartcount, 0)) over (partition by y.shipmentno) end  linecount                    -- 线路装载票数策略1 ae + be +ce + de （该值为线路装载率分子,分母为A的核载票数）
              -- 线路装载票数策略2 AB+AC+AD+BC+BD+CD（该值为线路装载率分子,分母为A的核载票数）
              , y.planneddeparturetime                                         as               planneddeparturetime         -- 规划发车时间
              , y.actualdeparturetime                                          as               actualdeparturetime          -- 实际发车时间
              , y.ispunctuality                                                as               punctuality                  -- 是否准点
              , y.plannedarrivaltime                                           as               plannedarrivaltime           -- 规划到车时间
              , y.actualarrivaltime                                            as               actualarrivaltime            -- 实际到车时间
              , y.islate                                                       as               late                         -- 是否晚点
              , y.startparentnetworkcode                                       as               startparentnetworkcode       -- 出发地代理商编码
              , y.senderregionalid                                             as               senderregionalid             -- 出发大区id
              , y.sender_manage_regionalcode                                   as               sender_manage_regionalcode   -- 出发管理大区code
              , y.startproviderid                                              as               startproviderid              -- 出发省id
              , y.receiverregionaid                                            as               receiverregionaid            -- 目的大区id
              , y.receiver_manage_regionacode                                  as               receiver_manage_regionacode  -- 目的大区id
              , y.endproviderid                                                as               endproviderid                -- 目的省id
              , y.endparentnetworkcode                                         as               endparentnetworkcode         -- 目的地代理商编码
              , y.shipmentname                                                 as               shipmentname                 -- 任务名字
              , y.hasout                                                       as               hasout                       -- 是否兜底
              , y.s_e_code                                                     as               s_e_code
              , y.strategy_line                                                as               strategy_line
              , y.grade                                                       as               grade
         from tmp_linepart y
                  left join tmp_ship_handling
                            on y.shipmentno = tmp_ship_handling.shipment_no
     ) x
         left join tmp_problem z
                   on x.shipmentno = z.shipment_no
                       and x.senderdtributecode = z.sender_distribute_code
                       and x.receiverdtributecode = z.receiver_distribute_code
         left join tmp_record y
                   on x.shipmentno = y.shipment_no
         left join jms_dim.dim_tmsnew_vehicle k -- 车线表  取车轴
                   on x.platenumber = k.plate_number
         left join tmp_error l -- 错扫数据
                   on x.shipmentno = l.shipment_no
                       and x.senderdtributecode = l.scan_site_code
         left join tmp_se on x.s_e_code = tmp_se.s_e_code
    distribute by 1;